package com.example.mygrade.repository;

import com.example.mygrade.domain.Student;
import com.example.mygrade.domain.TeacherMa;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import java.util.List;

public interface StudentRepository extends JpaRepository<Student,Integer> {
    /**
     * 按学生姓名查询
     * @param name
     * @return
     */
//    List<Student> findBySnameLike(String name);

    /**
     * 按学号和姓名进行查询
     * @param name
     * @param sno
     * @return
     */
    List<Student> findBySnameAndSno(String name,String sno);

    List<Student> findBySnameAndPassword(String name,String password);

    Student findBySnameLike(String name);
    List<Student> findBySno(String sno);

    @Modifying
    @Query(value = "insert into studentinfo(sno,sname,sex,sage,classid,gradeid,mid) values(student)",nativeQuery = true)
    public List<Student> insertStudent(Student student);


    @Query(nativeQuery = true,value = "select b.id,b.sname,c.sno,c.cno,d.cname from studentinfo b,sc c,course d" +
            "where b.sno = c.sno and c.cno = d.cno")
    List<Object[]> findBySnameAndCnameContain();

//    @Query(nativeQuery = true, value = "SELECT b.id, b.name, GROUP_CONCAT(a.name) as authorName from book b, author a, book_author ba"
//            + " where b.id = ba.book_id and a.id = ba.author_id and b.name like ?1 group by b.id, b.name")
//    List<Object[]> findByNameContaining(String name);
}
